use strict;
use DBI;
require XML::Twig;


# Return a connection with the bugzilla database.
my $self = {};
my $dbname = "bugs";
my $dbuname = "bugs";
my $bug_db_password = "sockmonkey";
my $bug_db_host = "localhost";
my $bug_db_user_id = "9";


my $numArgs = $#ARGV + 1;

if( $numArgs < 1) {
        die "Not enough arguments supplied";
}

my $filepath = $ARGV[0];
my $xTwig = XML::Twig->new();
my $xDoc = $xTwig->safe_parsefile($filepath);
my $xRoot = $xDoc->root( );
unless( $xRoot ) {
    die "Error : XML in $filepath could not be parsed : $!";
}

my $comment = "Code Review Status Changed To: " . $xRoot->first_child('review_status')->text() . "\n";
$comment .= $xRoot->first_child('review_url')->text() . "\n";

foreach my $xElt ( $xRoot->children('reviewer')) {
        $comment .= $xElt->att('status') . " by " . $xElt->att('email') . "\n";
}

my $bugid = $xRoot->first_child('defect_id')->text();
my $status = $xRoot->first_child('review_status')->text();

#print $comment;

    my $dbh = DBI->connect("DBI:mysql:dbname=$dbname;host=$bug_db_host",
		 $dbuname, $bug_db_password,
		 { RaiseError => 1, AutoCommit => 1 });
$self->{dbh} = $dbh;
# Create the necessary prepared statements.
my $insert_comment =
    $self->{dbh}->prepare_cached('INSERT INTO longdescs ' .
				 '(bug_id, who, bug_when, thetext) ' .
				 'VALUES (?, ?, now(), ?)');
my $update_bug =
    $self->{dbh}->prepare_cached('UPDATE bugs SET delta_ts = now() ' .
				 'WHERE bug_id = ?');
# Execute the statements.
$insert_comment->execute($bugid, $bug_db_user_id, $comment);
$update_bug->execute($bugid);

if( $status =~ m/NEEDSIMPROVEMENT/im ) {
	# figure out the 'id' for the flag to update
	my $find_flag = $self->{dbh}->prepare_cached( 'SELECT * from flags WHERE type_id = ? AND bug_id = ? AND status =? AND setter_id = ?' );
	$find_flag->execute( '1', $bugid, '?', $bug_db_user_id );
	my $data = $find_flag->fetchrow_hashref( 'NAME_lc' );
	$find_flag->finish( );
	my $flag_id = $data->{'id'};
	if( $flag_id ) {
		my $update_flag = $self->{dbh}->prepare_cached( 'UPDATE flags set status = ? where id = ?' );
		$update_flag->execute( '-', $flag_id );
	} else {
		my $get_id = $self->{dbh}->prepare_cached( 'SELECT MAX(id) as max_id FROM flags' );
		$get_id->execute( );
		my $id_data = $get_id->fetchrow_hashref( 'NAME_lc' );
		$get_id->finish( );
		my $id = $id_data->{'max_id'} + 1;
		my $add_flag = $self->{dbh}->prepare_cached('INSERT INTO flags ' .
				'(id, type_id, status, bug_id, creation_date, modification_date, setter_id)' .
				'VALUES (?,?,?,?,now(),now(),?)');
		$add_flag->execute( $id, '1', '-', $bugid, $bug_db_user_id );
	}
} elsif( $status =~ m/APPROVED/im ) {
	# figure out the 'id' for the flag to update
	my $find_flag = $self->{dbh}->prepare_cached( 'SELECT * from flags WHERE type_id = ? AND bug_id = ? AND status = ? AND setter_id = ?' );
	$find_flag->execute( '1', $bugid, '?', $bug_db_user_id );
	my $data = $find_flag->fetchrow_hashref( 'NAME_lc' );
	$find_flag->finish( );
	my $flag_id = $data->{'id'};
	if( $flag_id ) {
		my $update_flag = $self->{dbh}->prepare_cached( 'UPDATE flags set status = ? where id = ?' );
		$update_flag->execute( '+', $flag_id );
	} else {
		my $get_id = $self->{dbh}->prepare_cached( 'SELECT MAX(id) as max_id FROM flags' );
		$get_id->execute( );
		my $id_data = $get_id->fetchrow_hashref( 'NAME_lc' );
		$get_id->finish( );
		my $id = $id_data->{'max_id'} + 1;
		my $add_flag = $self->{dbh}->prepare_cached('INSERT INTO flags ' .
				'(id, type_id, status, bug_id, creation_date, modification_date, setter_id)' .
				'VALUES (?,?,?,?,now(),now(),?)');
		$add_flag->execute( $id, '1', '+', $bugid, $bug_db_user_id );
	}	} else { 
	die "Some type of invalid change here";
}
